﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ELS.User
{
    public class PermissionDataAccess
    {
        string connectionString = UserCommon.connectionString;
        

        internal DataTable SelectAllPermission()
        {
            DataTable dtAllPermission = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"Select * from Permission", sqlConnection);
                    SqlDataAdapter dtAdapter = new SqlDataAdapter(cmd);
                    dtAdapter.Fill(dtAllPermission);
                    return dtAllPermission;
                }
                catch (Exception e)
                {
                    return null;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }
        

        internal DataTable SelectPermissionByGroupId(int GroupId)
        {
            DataTable dtAllPermission = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"select 
                                                        Permission.ID as PER_ID,
                                                        Permission.CODE as PER_CD,
                                                        Permission.DESCRIPTION as PER_DESC,
                                                        GroupPermission.ID as GRPPER_ID,                                                        
                                                        GroupPermission.PERMISSION_ID as GRPPER_PER_ID,
                                                        GroupPermission.GROUP_ID as GRPPER_GRP_ID
                                                    from Permission, GroupPermission
                                                        where Permission.ID = GroupPermission.PERMISSION_ID
                                                        and GroupPermission.GROUP_ID = @GroupId", sqlConnection);
                    cmd.Parameters.Add("@GroupId", SqlDbType.Int).Value = GroupId;
                    SqlDataAdapter dtAdapter = new SqlDataAdapter(cmd);
                    dtAdapter.Fill(dtAllPermission);
                    return dtAllPermission;
                }
                catch (Exception e)
                {
                    return null;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        internal DataTable SelectPermissionByCode(string permissionCode)
        {
            DataTable dtPermission = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"select 
                                                        Permission.ID as PER_ID,
                                                        Permission.CODE as PER_CD,
                                                        Permission.DESCRIPTION as PER_DESC                                                        
                                                    from Permission 
                                                        where Permission.CODE = @code", sqlConnection);
                    cmd.Parameters.Add("@code", SqlDbType.NVarChar).Value = permissionCode;
                    SqlDataAdapter dtAdapter = new SqlDataAdapter(cmd);
                    dtAdapter.Fill(dtPermission);
                    return dtPermission;
                }
                catch (Exception e)
                {
                    return null;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        internal int UpdatePermission(int permissionId, string perCode, string perDes)
        {
            int rowUpdate = 0;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"UPDATE Permission SET
	                                                        Permission.CODE = @code,
                                                            Permission.DESCRIPTION = @desc                                                            
                                                             WHERE Permission.ID = @id", sqlConnection);
                    cmd.Parameters.Add("@code", SqlDbType.NVarChar).Value = perCode;
                    cmd.Parameters.Add("@desc", SqlDbType.NVarChar).Value = perDes;
                    cmd.Parameters.Add("@id", SqlDbType.Int).Value = permissionId;
                    rowUpdate = cmd.ExecuteNonQuery();
                    return rowUpdate;
                }
                catch (Exception e)
                {
                    return rowUpdate;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        internal int InsertPermission(string perCode, string perDes)
        {
            int rowInsert = 0;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"INSERT INTO Permission(CODE,DESCRIPTION) VALUES
	                                                        (@code,
                                                             @desc)", sqlConnection);
                    cmd.Parameters.Add("@code", SqlDbType.NVarChar).Value = perCode;
                    cmd.Parameters.Add("@desc", SqlDbType.NVarChar).Value = perDes;
                    rowInsert = cmd.ExecuteNonQuery();
                    return rowInsert;
                }
                catch (Exception e)
                {
                    return rowInsert;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        internal int DeletePermission(string listPermission)
        {
            int rowDelete = 0;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"DELETE FROM Permission WHERE Permission.ID IN (" + listPermission + ")", sqlConnection);
                    rowDelete = cmd.ExecuteNonQuery();
                    return rowDelete;
                }
                catch (Exception e)
                {
                    return rowDelete;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        internal DataTable SelectPermissionById(int perId)
        {
            DataTable dtPermission = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"select 
                                                        Permission.ID as PER_ID,
                                                        Permission.CODE as PER_CD,
                                                        Permission.DESCRIPTION as PER_DESC                                                        
                                                    from Permission 
                                                        where Permission.ID = @id", sqlConnection);
                    cmd.Parameters.Add("@id", SqlDbType.Int).Value = perId;
                    SqlDataAdapter dtAdapter = new SqlDataAdapter(cmd);
                    dtAdapter.Fill(dtPermission);
                    return dtPermission;
                }
                catch (Exception e)
                {
                    return null;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }
    }
}
